package bookmanage;

import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JOptionPane;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class ConnectDB {

	Connection con;
	PreparedStatement psmt;
	Statement stmt;
	ResultSet rs;
	
	String sql = "";
	String url="jdbc:oracle:thin:@localhost:1521:xe";
	String[] tableName = {"MEMBERTBL","CODETBL","BOOKINFO","RENT_RETURN","RESERVEBOOK","CAN_RENT","ARREARS","ZIPCODE"};
	
	public ConnectDB() {}
	
	public void connect(){
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			con = DriverManager.getConnection(url,"system","1234");
			sql = "select count(*) as cnt from dba_users where username='BOOK'";
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				int cnt = rs.getInt("cnt");
				System.out.println(cnt);
				if(cnt==0){
					sql = "create user book identified by book";
					stmt.execute(sql);
					
					sql = "grant resource, connect to book";
					stmt.execute(sql);
				}
				con.close();
				con=null;
				rs.close();
				stmt.close();
				
			}
			con = DriverManager.getConnection(url,"book","book");
		} 
		catch(Exception e){
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "데이터베이스가 없거나 데이터베이스 관리가 계정이 다릅니다.");
		}
	}
	
	public void createTable(){
		try {
			DatabaseMetaData dmd = con.getMetaData();
			for(int i=0;i<tableName.length;i++){
				rs = dmd.getTables(null, null, tableName[i], null);
				if(!rs.next()){
					switch (i) {
					case 0:
						createMember();
						break;
					case 1:
						createCodeTBL();
						break;
					case 2:
						createBookInfo();
						break;
					case 3:
						createRent_Return();
						break;
					case 4:
						createReserveBook();
						break;
					case 5:
						createCan_Rent();
						break;
					case 6:
						createArrears();
						break;
					case 7:
						createZipCode();
						break;
					}
					
					Thread.sleep(100);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

	public void createMember(){
		StringBuilder sb = new StringBuilder();
		try {
			sb.append("create table MEMBERTBL( ");
			sb.append("m_id varchar2(20) primary key,");
			sb.append("classname nvarchar2(20),");
			sb.append("m_name nvarchar2(10) not null,");
			sb.append("m_pwd varchar2(20) not null,");
			sb.append("m_gender nvarchar2(2) not null,");
			sb.append("m_birth date not null,");
			sb.append("m_zipcode char(7) not null,");
			sb.append("m_addr nvarchar2(100) not null,");
			sb.append("m_phone char(13) not null,");
			sb.append("m_email varchar2(40),");
			sb.append("m_joindate date default(sysdate) not null,");
			sb.append("m_grade nvarchar2(10) not null,");
			sb.append("m_photo varchar2(20) )");
			
			stmt = con.createStatement();
			stmt.executeUpdate(sb.toString());
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void createCodeTBL(){
		StringBuilder sb = new StringBuilder();
		try {
			sb.append("create table CODETBL( ");
			sb.append("code_no number primary key,");
			sb.append("big_name nvarchar2(20) not null,");
			sb.append("small_name nvarchar2(20) )");
			
			stmt = con.createStatement();
			stmt.executeUpdate(sb.toString());
			
			sql="create sequence seq_code_no";
			stmt.executeUpdate(sql);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void createBookInfo(){
		StringBuilder sb = new StringBuilder();
		try {
			sb.append("create table BOOKINFO( ");
			sb.append("book_no number primary key,");
			sb.append("b_title nvarchar2(30) not null,");
			sb.append("b_author nvarchar2(20) not null,");
			sb.append("b_publisher nvarchar2(20) not null,");
			sb.append("b_publishdate date,");
			sb.append("big_name nvarchar2(20),");
			sb.append("small_name nvarchar2(20) )");
			
			stmt = con.createStatement();
			stmt.executeUpdate(sb.toString());
			
			sql="create sequence seq_book_no";
			stmt.executeUpdate(sql);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void createRent_Return(){
		StringBuilder sb = new StringBuilder();
		try {
			sb.append("create table RENT_RETURN( ");
			sb.append("rr_no number primary key,");
			sb.append("m_id varchar2(20) references memberTBL(m_id),");
			sb.append("book_no number references  bookinfo(book_no),");
			sb.append("rent_date date default(sysdate),");
			sb.append("schedule_date date default(sysdate+7),");
			sb.append("return_date date )");
			
			stmt = con.createStatement();
			stmt.executeUpdate(sb.toString());
			
			sql="create sequence seq_rr_no";
			stmt.executeUpdate(sql);
				
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void createReserveBook(){
		StringBuilder sb = new StringBuilder();
		try {
			sb.append("create table RESERVEBOOK( ");
			sb.append("reserve_no number primary key,");
			sb.append("m_id varchar2(20) references memberTBL(m_id),");
			sb.append("rr_no number references rent_return(rr_no),");
			sb.append("reserve_date date default(sysdate),");
			sb.append("is_rent char(1) default('N') )");
			
			stmt = con.createStatement();
			stmt.executeUpdate(sb.toString());
			
			sql="create sequence seq_reserve_no";
			stmt.executeUpdate(sql);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void createCan_Rent(){
		StringBuilder sb = new StringBuilder();
		try {
			sb.append("create table CAN_RENT( ");
			sb.append("can_rent_no number primary key,");
			sb.append("m_id varchar2(20) references memberTBL(m_id),");
			sb.append("can_rent_date date )");
			
			stmt = con.createStatement();
			stmt.executeUpdate(sb.toString());
			
			sql="create sequence seq_cr_no";
			stmt.executeUpdate(sql);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void createArrears(){
		StringBuilder sb = new StringBuilder();
		try {
			sb.append("create table ARREARS( ");
			sb.append("arrears_no number primary key,");
			sb.append("m_id varchar2(20) references memberTBL(m_id),");
			sb.append("cashier varchar2(20) references memberTBL(m_id),");
			sb.append("arrears_money number not null,");
			sb.append("arrears_date date,");
			sb.append("payment_money number,");
			sb.append("payment_date date )");
			
			stmt = con.createStatement();
			stmt.executeUpdate(sb.toString());
			
			sql="create sequence seq_arrears_no";
			stmt.executeUpdate(sql);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void createZipCode(){
		StringBuilder sb = new StringBuilder();
		try {
			sb.append("create table ZIPCODE( ");
			sb.append("zipcode char(7),");
			sb.append("sido varchar2(10),");
			sb.append("gugun varchar2(40),");
			sb.append("dong varchar2(110),");
			sb.append("bunji varchar2(40) )");
			
			stmt = con.createStatement();
			stmt.executeUpdate(sb.toString());
			
			Workbook wb = Workbook.getWorkbook(new File("zipcode/zipcode.xls")); //워크북 가져오기

			Sheet sheet = wb.getSheet(0); // 첫번째 시트
			for(int i=0;i<sheet.getRows();i++){
				String sql="insert into zipcode values(?,?,?,?,?)";
				PreparedStatement pt = con.prepareStatement(sql);
				for(int j=0;j<sheet.getColumns();j++){
					Cell cell = sheet.getCell(j,i); // (1,0) ~ (1,컬럼수)											
					pt.setString(j+1, cell.getContents());
				}
				int cnt = pt.executeUpdate();
				if(pt!=null) pt.close();
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void connectClose(){
		try {
			if(psmt != null) psmt.close();
			if(rs != null) rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
